﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class PurchaseOrderCommitteeDAL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public PurchaseOrderCommitteeDAL()
        {

        }

        public PurchaseOrderCommitteeInfo GetPurchaseOrderCommittee(int seq)
        {
            string strSQL;
            DataSet ds;
            DataRowCollection dr;
            PurchaseOrderCommitteeInfo info = new PurchaseOrderCommitteeInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT pc.*"
                            + ", f.ICM_COM_FUNC_NAME"
                            + ", p.ICM_COM_POS_NAME"
                            + ", e.FIRST_NAME"
                            + ", e.LAST_NAME"
                            + ", c.CLASS_NAME"
                            + ", pos.POS_NAME"
                            + ", js.JOB_SITE_NAME"
                            + ", ct.ICM_COM_TYPE_NAME"
                        + " FROM ICM_PURCHASE_ORDER_COMMITTEE pc"
                            + " INNER JOIN ICM_SYS_COMMITTEE_TYPE ct ON pc.ICM_COM_TYPE_SEQ = ct.ICM_COM_TYPE_SEQ"
                            + " LEFT JOIN ICM_MAS_COMMITTEE_FUNCTION f ON pc.ICM_COM_FUNC_SEQ = f.ICM_COM_FUNC_SEQ"
                            + " LEFT JOIN ICM_MAS_COMMITTEE_POSITION p ON pc.ICM_COM_POS_SEQ = p.ICM_COM_POS_SEQ"
                            + " LEFT JOIN HRM_PS_EMPLOYEES e ON pc.EMP_SEQ = e.EMP_SEQ"
                            + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                            + " LEFT JOIN HRM_MP_POSITIONS pos ON pos.POS_SEQ = e.POS_SEQ"
                            + " LEFT JOIN HRM_MP_JOB_SITES js ON e.WORKING_JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                        + " WHERE pc.ICM_PO_COM_SEQ = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    PurchaseOrderInfo infoPo = new PurchaseOrderInfo();
                    MasterCommitteeFunctionInfo infoComFunction = new MasterCommitteeFunctionInfo();
                    MasterCommitteePositionInfo infoComPosition = new MasterCommitteePositionInfo();
                    HrmEmployeeInfo infoEmployee = new HrmEmployeeInfo();
                    HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                    HrmClassInfo infoClass = new HrmClassInfo();
                    HrmPositionInfo infoPosition = new HrmPositionInfo();
                    RecordInfo infoRecord = new RecordInfo();
                    SysCommitteeTypeInfo infoType = new SysCommitteeTypeInfo();
                    info.Seq = Convert.ToInt32(dr[0]["ICM_PO_COM_SEQ"]);
                    infoPo.Seq = Convert.ToInt32(dr[0]["ICM_PO_SEQ"]);
                    info.Ref_no = Convert.ToInt32(dr[0]["ICM_PO_COM_REF_NO"]);
                    infoType.Seq = Convert.ToInt32(dr[0]["ICM_COM_TYPE_SEQ"].ToString());
                    infoType.Name = dr[0]["ICM_COM_TYPE_NAME"].ToString();
                    info.Ex_name = dr[0]["ICM_PO_COM_EX_NAME"].ToString();
                    info.Ex_descr = dr[0]["ICM_PO_COM_EX_DESCR"].ToString();
                    infoComFunction.Seq = (Convert.IsDBNull(dr[0]["ICM_COM_FUNC_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_COM_FUNC_SEQ"]));
                    infoComFunction.Name = dr[0]["ICM_COM_FUNC_NAME"].ToString();
                    infoComPosition.Seq = (Convert.IsDBNull(dr[0]["ICM_COM_POS_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_COM_POS_SEQ"]));
                    infoComPosition.Name = dr[0]["ICM_COM_POS_NAME"].ToString();
                    infoClass.Name = dr[0]["CLASS_NAME"].ToString();
                    infoJobSite.Name = dr[0]["JOB_SITE_NAME"].ToString();
                    infoPosition.Name = dr[0]["POS_NAME"].ToString();
                    infoEmployee.Seq = (Convert.IsDBNull(dr[0]["EMP_SEQ"]) ? null : (Nullable<int>)(dr[0]["EMP_SEQ"]));
                    infoEmployee.Name = dr[0]["FIRST_NAME"].ToString() + " " + dr[0]["LAST_NAME"].ToString();
                    if (infoType.Seq == 1) //บุคลากรภายใน
                    {
                        info.Name = infoEmployee.Name;
                        info.Descr = (infoJobSite.Name.Length > 0 ? " <b>หน่วยงาน : </b>" + infoJobSite.Name : "")
                            + (infoPosition.Name.Length > 0 ? " <b>ตำแหน่ง :</b>" + infoPosition.Name : "")
                            + (infoClass.Name.Length > 0 ? " <b>ระดับ :</b>" + infoClass.Name : "");
                    }
                    else
                    {
                        info.Name = info.Ex_name;
                        info.Descr = info.Ex_descr;
                    }

                    infoRecord.Created_by = (Convert.IsDBNull(dr[0]["CREATED_BY"]) ? null : (Nullable<int>)(dr[0]["CREATED_BY"]));
                    infoRecord.Created_when = (Convert.IsDBNull(dr[0]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["CREATED_WHEN"]));
                    infoRecord.Updated_by = (Convert.IsDBNull(dr[0]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[0]["UPDATED_BY"]));
                    infoRecord.Updated_when = (Convert.IsDBNull(dr[0]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["UPDATED_WHEN"]));
                    infoRecord.Restored_by = (Convert.IsDBNull(dr[0]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[0]["RESTORED_BY"]));
                    infoRecord.Restored_when = (Convert.IsDBNull(dr[0]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["RESTORED_WHEN"]));
                    //infoStatus.Status = (dr[0]["RECORD_STATUS"]).ToString();
                    //infoRecord.InfoStatus = infoStatus;
                    info.InfoPo = infoPo;
                    info.InfoType = infoType;
                    info.InfoExployee = infoEmployee;
                    info.InfoFunction = infoComFunction;
                    info.InfoPosition = infoComPosition;
                    info.InfoRecord = infoRecord;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public IList<PurchaseOrderCommitteeInfo> GetPurchaseOrderCommitteeFromPurchaseRequest(int bidder_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderCommitteeInfo> infoList = new List<PurchaseOrderCommitteeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bidder_seq", bidder_seq);

                strSQL = "SELECT pc.*"
                            + ", f.ICM_COM_FUNC_NAME"
                            + ", p.ICM_COM_POS_NAME"
                            + ", e.FIRST_NAME"
                            + ", e.LAST_NAME"
                            + ", c.CLASS_NAME"
                            + ", pos.POS_NAME"
                            + ", js.JOB_SITE_NAME"
                            + ", ct.ICM_COM_TYPE_NAME"
                        + " FROM ICM_BIDDER b"
                            + " INNER JOIN ICM_PURCHASE_REQUEST_COMMITTEE pc ON b.ICM_PR_SEQ = pc.ICM_PR_SEQ"
                            + " INNER JOIN ICM_SYS_COMMITTEE_TYPE ct ON pc.ICM_COM_TYPE_SEQ = ct.ICM_COM_TYPE_SEQ"
                            + " LEFT JOIN ICM_MAS_COMMITTEE_FUNCTION f ON pc.ICM_COM_FUNC_SEQ = f.ICM_COM_FUNC_SEQ"
                            + " LEFT JOIN ICM_MAS_COMMITTEE_POSITION p ON pc.ICM_COM_POS_SEQ = p.ICM_COM_POS_SEQ"
                            + " LEFT JOIN HRM_PS_EMPLOYEES e ON pc.EMP_SEQ = e.EMP_SEQ"
                            + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                            + " LEFT JOIN HRM_MP_POSITIONS pos ON pos.POS_SEQ = e.POS_SEQ"
                            + " LEFT JOIN HRM_MP_JOB_SITES js ON e.WORKING_JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                        + " WHERE b.ICM_BIDDER_SEQ = @bidder_seq"
                            + " AND pc.RECORD_STATUS <> @delete_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderCommitteeInfo info = new PurchaseOrderCommitteeInfo();
                        MasterCommitteeFunctionInfo infoComFunction = new MasterCommitteeFunctionInfo();
                        MasterCommitteePositionInfo infoComPosition = new MasterCommitteePositionInfo();
                        HrmEmployeeInfo infoEmployee = new HrmEmployeeInfo();
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                        HrmClassInfo infoClass = new HrmClassInfo();
                        HrmPositionInfo infoPosition = new HrmPositionInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                        SysCommitteeTypeInfo infoType = new SysCommitteeTypeInfo();
                        info.Seq = Convert.ToInt32(dr[i]["ICM_PR_COM_SEQ"]);
                        info.Ref_no = Convert.ToInt32(dr[i]["ICM_PR_COM_REF_NO"]);
                        info.DeleteReason = dr[i]["DELETE_REASON"].ToString();
                        infoType.Seq = Convert.ToInt32(dr[i]["ICM_COM_TYPE_SEQ"].ToString());
                        infoType.Name = dr[i]["ICM_COM_TYPE_NAME"].ToString();
                        info.Ex_name = dr[i]["ICM_PR_COM_EX_NAME"].ToString();
                        info.Ex_descr = dr[i]["ICM_PR_COM_EX_DESCR"].ToString();
                        infoComFunction.Seq = (Convert.IsDBNull(dr[i]["ICM_COM_FUNC_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_COM_FUNC_SEQ"]));
                        infoComFunction.Name = dr[i]["ICM_COM_FUNC_NAME"].ToString();
                        infoComPosition.Seq = (Convert.IsDBNull(dr[i]["ICM_COM_POS_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_COM_POS_SEQ"]));
                        infoComPosition.Name = dr[i]["ICM_COM_POS_NAME"].ToString();
                        infoClass.Name = dr[i]["CLASS_NAME"].ToString();
                        infoJobSite.Name = dr[i]["JOB_SITE_NAME"].ToString();
                        infoPosition.Name = dr[i]["POS_NAME"].ToString();
                        infoEmployee.Seq = (Convert.IsDBNull(dr[i]["EMP_SEQ"]) ? null : (Nullable<int>)(dr[i]["EMP_SEQ"]));
                        infoEmployee.Name = dr[i]["FIRST_NAME"].ToString() + " " + dr[i]["LAST_NAME"].ToString();
                        if (infoType.Seq == 1) //บุคลากรภายใน
                        {
                            info.Name = infoEmployee.Name;
                            info.Descr = (infoJobSite.Name.Length > 0 ? " <b>หน่วยงาน : </b>" + infoJobSite.Name : "")
                                + (infoPosition.Name.Length > 0 ? " <b>ตำแหน่ง :</b>" + infoPosition.Name : "")
                                + (infoClass.Name.Length > 0 ? " <b>ระดับ :</b>" + infoClass.Name : "");
                        }
                        else
                        {
                            info.Name = info.Ex_name;
                            info.Descr = info.Ex_descr;
                        }

                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        infoStatus.Status = (dr[i]["RECORD_STATUS"]).ToString();
                        infoRecord.InfoStatus = infoStatus;
                        info.InfoType = infoType;
                        info.InfoExployee = infoEmployee;
                        info.InfoFunction = infoComFunction;
                        info.InfoPosition = infoComPosition;
                        info.InfoRecord = infoRecord;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PurchaseOrderCommitteeInfo> GetPurchaseOrderCommitteeFromBid(int bidder_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderCommitteeInfo> infoList = new List<PurchaseOrderCommitteeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bidder_seq", bidder_seq);

                strSQL = "SELECT pc.*"
                            + ", f.ICM_COM_FUNC_NAME"
                            + ", p.ICM_COM_POS_NAME"
                            + ", e.FIRST_NAME"
                            + ", e.LAST_NAME"
                            + ", c.CLASS_NAME"
                            + ", pos.POS_NAME"
                            + ", js.JOB_SITE_NAME"
                            + ", ct.ICM_COM_TYPE_NAME"
                        + " FROM ICM_BIDDER bd"
                            + " INNER JOIN ICM_BID b ON bd.ICM_BID_SEQ = b.ICM_BID_SEQ" 
                            + " INNER JOIN ICM_BID_COMMITTEE pc ON b.ICM_BID_SEQ = pc.ICM_BID_SEQ"
                            + " INNER JOIN ICM_SYS_COMMITTEE_TYPE ct ON pc.ICM_COM_TYPE_SEQ = ct.ICM_COM_TYPE_SEQ"
                            + " LEFT JOIN ICM_MAS_COMMITTEE_FUNCTION f ON pc.ICM_COM_FUNC_SEQ = f.ICM_COM_FUNC_SEQ"
                            + " LEFT JOIN ICM_MAS_COMMITTEE_POSITION p ON pc.ICM_COM_POS_SEQ = p.ICM_COM_POS_SEQ"
                            + " LEFT JOIN HRM_PS_EMPLOYEES e ON pc.EMP_SEQ = e.EMP_SEQ"
                            + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                            + " LEFT JOIN HRM_MP_POSITIONS pos ON pos.POS_SEQ = e.POS_SEQ"
                            + " LEFT JOIN HRM_MP_JOB_SITES js ON e.WORKING_JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                        + " WHERE bd.ICM_BIDDER_SEQ = @bidder_seq"
                            + " AND pc.RECORD_STATUS <> @delete_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderCommitteeInfo info = new PurchaseOrderCommitteeInfo();
                        MasterCommitteeFunctionInfo infoComFunction = new MasterCommitteeFunctionInfo();
                        MasterCommitteePositionInfo infoComPosition = new MasterCommitteePositionInfo();
                        HrmEmployeeInfo infoEmployee = new HrmEmployeeInfo();
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                        HrmClassInfo infoClass = new HrmClassInfo();
                        HrmPositionInfo infoPosition = new HrmPositionInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                        SysCommitteeTypeInfo infoType = new SysCommitteeTypeInfo();
                        info.Seq = Convert.ToInt32(dr[i]["ICM_BID_COM_SEQ"]);
                        info.Ref_no = Convert.ToInt32(dr[i]["ICM_BID_COM_REF_NO"]);
                        info.DeleteReason = dr[i]["DELETE_REASON"].ToString();
                        infoType.Seq = Convert.ToInt32(dr[i]["ICM_COM_TYPE_SEQ"].ToString());
                        infoType.Name = dr[i]["ICM_COM_TYPE_NAME"].ToString();
                        info.Ex_name = dr[i]["ICM_BID_COM_EX_NAME"].ToString();
                        info.Ex_descr = dr[i]["ICM_BID_COM_EX_DESCR"].ToString();
                        infoComFunction.Seq = (Convert.IsDBNull(dr[i]["ICM_COM_FUNC_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_COM_FUNC_SEQ"]));
                        infoComFunction.Name = dr[i]["ICM_COM_FUNC_NAME"].ToString();
                        infoComPosition.Seq = (Convert.IsDBNull(dr[i]["ICM_COM_POS_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_COM_POS_SEQ"]));
                        infoComPosition.Name = dr[i]["ICM_COM_POS_NAME"].ToString();
                        infoClass.Name = dr[i]["CLASS_NAME"].ToString();
                        infoJobSite.Name = dr[i]["JOB_SITE_NAME"].ToString();
                        infoPosition.Name = dr[i]["POS_NAME"].ToString();
                        infoEmployee.Seq = (Convert.IsDBNull(dr[i]["EMP_SEQ"]) ? null : (Nullable<int>)(dr[i]["EMP_SEQ"]));
                        infoEmployee.Name = dr[i]["FIRST_NAME"].ToString() + " " + dr[i]["LAST_NAME"].ToString();
                        if (infoType.Seq == 1) //บุคลากรภายใน
                        {
                            info.Name = infoEmployee.Name;
                            info.Descr = (infoJobSite.Name.Length > 0 ? " <b>หน่วยงาน : </b>" + infoJobSite.Name : "")
                                + (infoPosition.Name.Length > 0 ? " <b>ตำแหน่ง :</b>" + infoPosition.Name : "")
                                + (infoClass.Name.Length > 0 ? " <b>ระดับ :</b>" + infoClass.Name : "");
                        }
                        else
                        {
                            info.Name = info.Ex_name;
                            info.Descr = info.Ex_descr;
                        }

                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        infoStatus.Status = (dr[i]["RECORD_STATUS"]).ToString();
                        infoRecord.InfoStatus = infoStatus;
                        info.InfoType = infoType;
                        info.InfoExployee = infoEmployee;
                        info.InfoFunction = infoComFunction;
                        info.InfoPosition = infoComPosition;
                        info.InfoRecord = infoRecord;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PurchaseOrderCommitteeInfo> GetPurchaseOrderCommitteeWithParent(int po_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderCommitteeInfo> infoList = new List<PurchaseOrderCommitteeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@po_seq", po_seq);

                strSQL = "SELECT pc.*"
                            + ", f.ICM_COM_FUNC_NAME"
                            + ", p.ICM_COM_POS_NAME"
                            + ", e.FIRST_NAME"
                            + ", e.LAST_NAME"
                            + ", c.CLASS_NAME"
                            + ", pos.POS_NAME"
                            + ", js.JOB_SITE_NAME"
                            + ", ct.ICM_COM_TYPE_NAME"
                        + " FROM ICM_PURCHASE_ORDER_COMMITTEE pc"
                            + " INNER JOIN ICM_SYS_COMMITTEE_TYPE ct ON pc.ICM_COM_TYPE_SEQ = ct.ICM_COM_TYPE_SEQ"
                            + " LEFT JOIN ICM_MAS_COMMITTEE_FUNCTION f ON pc.ICM_COM_FUNC_SEQ = f.ICM_COM_FUNC_SEQ"
                            + " LEFT JOIN ICM_MAS_COMMITTEE_POSITION p ON pc.ICM_COM_POS_SEQ = p.ICM_COM_POS_SEQ"
                            + " LEFT JOIN HRM_PS_EMPLOYEES e ON pc.EMP_SEQ = e.EMP_SEQ"
                            + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                            + " LEFT JOIN HRM_MP_POSITIONS pos ON pos.POS_SEQ = e.POS_SEQ"
                            + " LEFT JOIN HRM_MP_JOB_SITES js ON e.WORKING_JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                        + " WHERE pc.ICM_PO_SEQ = @po_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderCommitteeInfo info = new PurchaseOrderCommitteeInfo();
                        MasterCommitteeFunctionInfo infoComFunction = new MasterCommitteeFunctionInfo();
                        MasterCommitteePositionInfo infoComPosition = new MasterCommitteePositionInfo();
                        HrmEmployeeInfo infoEmployee = new HrmEmployeeInfo();
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                        HrmClassInfo infoClass = new HrmClassInfo();
                        HrmPositionInfo infoPosition = new HrmPositionInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                        SysCommitteeTypeInfo infoType = new SysCommitteeTypeInfo();
                        info.Seq = Convert.ToInt32(dr[i]["ICM_PO_COM_SEQ"]);
                        info.Ref_no = Convert.ToInt32(dr[i]["ICM_PO_COM_REF_NO"]);
                        info.DeleteReason = dr[i]["DELETE_REASON"].ToString();
                        infoType.Seq = Convert.ToInt32(dr[i]["ICM_COM_TYPE_SEQ"].ToString());
                        infoType.Name = dr[i]["ICM_COM_TYPE_NAME"].ToString();
                        info.Ex_name = dr[i]["ICM_PO_COM_EX_NAME"].ToString();
                        info.Ex_descr = dr[i]["ICM_PO_COM_EX_DESCR"].ToString();
                        infoComFunction.Seq = (Convert.IsDBNull(dr[i]["ICM_COM_FUNC_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_COM_FUNC_SEQ"]));
                        infoComFunction.Name = dr[i]["ICM_COM_FUNC_NAME"].ToString();
                        infoComPosition.Seq = (Convert.IsDBNull(dr[i]["ICM_COM_POS_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_COM_POS_SEQ"]));
                        infoComPosition.Name = dr[i]["ICM_COM_POS_NAME"].ToString();
                        infoClass.Name = dr[i]["CLASS_NAME"].ToString();
                        infoJobSite.Name = dr[i]["JOB_SITE_NAME"].ToString();
                        infoPosition.Name = dr[i]["POS_NAME"].ToString();
                        infoEmployee.Seq = (Convert.IsDBNull(dr[i]["EMP_SEQ"]) ? null : (Nullable<int>)(dr[i]["EMP_SEQ"]));
                        infoEmployee.Name = dr[i]["FIRST_NAME"].ToString() + " " + dr[i]["LAST_NAME"].ToString();
                        if (infoType.Seq == 1) //บุคลากรภายใน
                        {
                            info.Name = infoEmployee.Name;
                            info.Descr = (infoJobSite.Name.Length > 0 ? " <b>หน่วยงาน : </b>" + infoJobSite.Name : "")
                                + (infoPosition.Name.Length > 0 ? " <b>ตำแหน่ง :</b>" + infoPosition.Name : "")
                                + (infoClass.Name.Length > 0 ? " <b>ระดับ :</b>" + infoClass.Name : "");
                        }
                        else
                        {
                            info.Name = info.Ex_name;
                            info.Descr = info.Ex_descr;
                        }

                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        infoStatus.Status = (dr[i]["RECORD_STATUS"]).ToString();
                        infoRecord.InfoStatus = infoStatus;
                        info.InfoType = infoType;
                        info.InfoExployee = infoEmployee;
                        info.InfoFunction = infoComFunction;
                        info.InfoPosition = infoComPosition;
                        info.InfoRecord = infoRecord;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public void Insert(IList<PurchaseOrderCommitteeInfo> infoList, Nullable<int> po_seq, PurchaseOrderInfo info, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManagerTrans.CreateParameters(12);

                    dbManagerTrans.AddParameters(0, "@icm_po_com_ref_no", infoList[i].Ref_no);
                    dbManagerTrans.AddParameters(1, "@icm_com_type_seq", infoList[i].InfoType.Seq);
                    dbManagerTrans.AddParameters(2, "@icm_po_com_ex_name", infoList[i].Ex_name);
                    dbManagerTrans.AddParameters(3, "@icm_po_com_ex_descr", infoList[i].Ex_descr);
                    dbManagerTrans.AddParameters(4, "@icm_com_func_seq", infoList[i].InfoFunction.Seq);
                    dbManagerTrans.AddParameters(5, "@icm_com_pos_seq", infoList[i].InfoPosition.Seq);
                    dbManagerTrans.AddParameters(6, "@emp_seq", infoList[i].InfoExployee.Seq);
                    dbManagerTrans.AddParameters(7, "@icm_po_seq", po_seq);
                    dbManagerTrans.AddParameters(8, "@created_by", info.InfoRecord.Created_by);
                    dbManagerTrans.AddParameters(9, "@created_when", info.InfoRecord.Created_when);
                    dbManagerTrans.AddParameters(10, "@record_status", infoList[i].InfoRecord.InfoStatus.Status);
                    dbManagerTrans.AddParameters(11, "@delete_reason", infoList[i].DeleteReason);

                    strSQL = "INSERT INTO ICM_PURCHASE_ORDER_COMMITTEE"
                        + " (ICM_PO_COM_REF_NO, ICM_COM_TYPE_SEQ, ICM_PO_COM_EX_NAME, ICM_PO_COM_EX_DESCR"
                        + ", ICM_COM_FUNC_SEQ, ICM_COM_POS_SEQ, EMP_SEQ, ICM_PO_SEQ"
                        + " , DELETE_REASON, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                        + " VALUES(@icm_po_com_ref_no"
                        + ", @icm_com_type_seq"
                        + ", @icm_po_com_ex_name"
                        + ", @icm_po_com_ex_descr"
                        + ", @icm_com_func_seq"
                        + ", @icm_com_pos_seq"
                        + ", @emp_seq"
                        + ", @icm_po_seq"
                        + ", @delete_reason"
                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when"
                        + " )";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch
            {
                throw;
            }
        }

        public void Insert(PurchaseOrderCommitteeInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(11);

                dbManager.AddParameters(0, "@ref_no", info.Ref_no);
                dbManager.AddParameters(1, "@com_type_seq", info.InfoType.Seq);
                dbManager.AddParameters(2, "@ex_name", info.Ex_name);
                dbManager.AddParameters(3, "@ex_descr", info.Ex_descr);
                dbManager.AddParameters(4, "@func_seq", info.InfoFunction.Seq);
                dbManager.AddParameters(5, "@pos_seq", info.InfoPosition.Seq);
                dbManager.AddParameters(6, "@emp_seq", info.InfoExployee.Seq);
                dbManager.AddParameters(7, "@po_seq", info.InfoPo.Seq);
                dbManager.AddParameters(8, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(9, "@created_when", info.InfoRecord.Created_when);
                dbManager.AddParameters(10, "@record_status", SystemInfo._WorkRecordStatus);

                strSQL = "INSERT INTO ICM_PURCHASE_ORDER_COMMITTEE"
                        + " (ICM_PO_COM_REF_NO, ICM_COM_TYPE_SEQ, ICM_PO_COM_EX_NAME, ICM_PO_COM_EX_DESCR"
                        + " , ICM_COM_FUNC_SEQ, ICM_COM_POS_SEQ, EMP_SEQ, ICM_PO_SEQ"
                        + " , RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                        + " VALUES(@ref_no"
                        + ", @com_type_seq"
                        + ", @ex_name"
                        + ", @ex_descr"
                        + ", @func_seq"
                        + ", @pos_seq"
                        + ", @emp_seq"
                        + ", @po_seq"
                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when"
                        + " )";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(PurchaseOrderCommitteeInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@icm_po_com_seq", info.Seq);
                dbManager.AddParameters(1, "@icm_po_com_ref_no", info.Ref_no);
                dbManager.AddParameters(2, "@icm_com_type_seq", info.InfoType.Seq);
                dbManager.AddParameters(3, "@icm_po_com_ex_name", info.Ex_name);
                dbManager.AddParameters(4, "@icm_po_com_ex_descr", info.Ex_descr);
                dbManager.AddParameters(5, "@icm_com_func_seq", info.InfoFunction.Seq);
                dbManager.AddParameters(6, "@icm_com_pos_seq", info.InfoPosition.Seq);
                dbManager.AddParameters(7, "@emp_seq", info.InfoExployee.Seq);
                dbManager.AddParameters(8, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(9, "@updated_when", info.InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_PURCHASE_ORDER_COMMITTEE  "
                         + " SET ICM_COM_TYPE_SEQ = @icm_com_type_seq"
                         + ", ICM_PO_COM_EX_NAME = @icm_po_com_ex_name"
                         + ", ICM_PO_COM_EX_DESCR = @icm_po_com_ex_descr"
                         + ", ICM_COM_FUNC_SEQ = @icm_com_func_seq"
                         + ", ICM_COM_POS_SEQ = @icm_com_pos_seq"
                         + ", EMP_SEQ = @emp_seq"
                         + ", UPDATED_BY = @updated_by"
                         + ", UPDATED_WHEN = @updated_when"
                         + " WHERE ICM_PO_COM_SEQ = @icm_po_com_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteAll(IList<PurchaseOrderCommitteeInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", infoList[0].InfoRecord.Updated_by);
                dbManager.AddParameters(2, "@updated_when", infoList[0].InfoRecord.Updated_when);
                dbManager.AddParameters(3, "@delete_reason", infoList[0].DeleteReason);

                strSQL = "UPDATE ICM_PURCHASE_ORDER_COMMITTEE"
                            + " SET DELETE_REASON = @delete_reason"
                            + ", RECORD_STATUS = @record_status"
                            + ", UPDATED_BY =  @updated_by"
                            + ", UPDATED_WHEN =  @updated_when"
                        + " WHERE ICM_PO_COM_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteFromDB(IList<PurchaseOrderCommitteeInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();

                strSQL = "DELETE FROM ICM_PURCHASE_ORDER_COMMITTEE"
                    + " WHERE ICM_PO_COM_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
